const Database = require('better-sqlite3');
const db = new Database('./data/delivery-management.db');

console.log('=== 验证实例表系统完整性 ===');
console.log('日期:', new Date().toLocaleString('zh-CN'));

try {
  // 1. 统计总体情况
  const devices = db.prepare('SELECT COUNT(*) as count FROM devices').get();
  const mappings = db.prepare('SELECT COUNT(*) as count FROM device_type_mappings').get();
  const instanceTables = db.prepare('SELECT COUNT(*) as count FROM sqlite_master WHERE type=? AND name LIKE ?').get('table', 'device_instances_%');
  
  console.log('\n📊 系统概览:');
  console.log(`  • 总设备数: ${devices.count}`);
  console.log(`  • 设备映射数: ${mappings.count}`);
  console.log(`  • 实例表数: ${instanceTables.count}`);

  // 2. 验证每个设备的实例表逻辑（处理重复映射问题）
  const deviceList = db.prepare(`
    SELECT DISTINCT d.device_id, d.name, d.specialty, d.classification_code, 
           dtm.english_name, dtm.specialty as mapping_specialty
    FROM devices d
    LEFT JOIN device_type_mappings dtm ON d.classification_code = dtm.classification_code
    WHERE dtm.specialty = d.specialty OR dtm.specialty IS NULL
    ORDER BY d.specialty, d.name
  `).all();

  const specialtyPrefixes = {
    '配管专业': 'piping',
    '电气专业': 'electrical', 
    '暖通专业': 'hvac',
    '通信专业': 'communication',
    '仪表专业': 'instrumentation'
  };

  let validDevices = 0;
  let errors = [];

  console.log('\n🔍 验证设备实例表映射:');
  
  deviceList.forEach(device => {
    // 检查映射完整性
    if (!device.english_name || !device.mapping_specialty) {
      errors.push(`${device.device_id}: 缺少设备类型映射`);
      return;
    }

    // 检查专业前缀
    const specialtyPrefix = specialtyPrefixes[device.mapping_specialty];
    if (!specialtyPrefix) {
      errors.push(`${device.device_id}: 未知专业 ${device.mapping_specialty}`);
      return;
    }

    // 检查表存在性
    const expectedTableName = `device_instances_${specialtyPrefix}_${device.english_name}`;
    const tableExists = db.prepare('SELECT name FROM sqlite_master WHERE type=? AND name=?')
      .get('table', expectedTableName);
    
    if (!tableExists) {
      errors.push(`${device.device_id}: 缺少实例表 ${expectedTableName}`);
      return;
    }

    validDevices++;
  });

  // 3. 显示验证结果
  const successRate = (validDevices / deviceList.length * 100).toFixed(1);
  console.log(`  ✅ 验证通过: ${validDevices}/${deviceList.length} (${successRate}%)`);

  if (errors.length > 0) {
    console.log(`\n❌ 发现 ${errors.length} 个问题:`);
    errors.slice(0, 5).forEach(error => {
      console.log(`  • ${error}`);
    });
    if (errors.length > 5) {
      console.log(`  • ... 还有 ${errors.length - 5} 个问题`);
    }
  }

  // 4. 按专业统计实例表
  console.log('\n📋 按专业统计实例表:');
  Object.entries(specialtyPrefixes).forEach(([chineseName, prefix]) => {
    const count = db.prepare('SELECT COUNT(*) as count FROM sqlite_master WHERE type=? AND name LIKE ?')
      .get('table', `device_instances_${prefix}_%`);
    console.log(`  • ${chineseName}: ${count.count}个表`);
  });

  // 5. 检查数据完整性
  console.log('\n🗄️ 检查数据状态:');
  const tablesWithData = db.prepare(`
    SELECT name FROM sqlite_master 
    WHERE type='table' AND name LIKE 'device_instances_%'
  `).all();
  
  let totalInstances = 0;
  let tablesWithDataCount = 0;
  
  tablesWithData.forEach(table => {
    try {
      const count = db.prepare(`SELECT COUNT(*) as count FROM [${table.name}]`).get().count;
      totalInstances += count;
      if (count > 0) {
        tablesWithDataCount++;
      }
    } catch (e) {
      // 忽略查询错误
    }
  });

  console.log(`  • 总实例数: ${totalInstances}`);
  console.log(`  • 有数据的表: ${tablesWithDataCount}/${tablesWithData.length}`);

  // 6. 测试关键设备
  console.log('\n🧪 测试关键设备逻辑:');
  
  // 测试阀门设备
  const valveDevice = deviceList.find(d => d.device_id === '配管-0022');
  if (valveDevice) {
    const specialtyPrefix = specialtyPrefixes[valveDevice.mapping_specialty];
    const tableNameForValve = `device_instances_${specialtyPrefix}_${valveDevice.english_name}`;
    console.log(`  • 通用阀门 → ${tableNameForValve} ✅`);
  }

  // 测试其他几个设备
  const testDevices = deviceList.slice(0, 3);
  testDevices.forEach(device => {
    if (device.english_name && device.mapping_specialty) {
      const specialtyPrefix = specialtyPrefixes[device.mapping_specialty];
      if (specialtyPrefix) {
        const tableName = `device_instances_${specialtyPrefix}_${device.english_name}`;
        console.log(`  • ${device.name} → ${tableName} ✅`);
      }
    }
  });

  // 7. 最终评估
  console.log('\n🎯 系统状态评估:');
  
  if (validDevices === deviceList.length && errors.length === 0) {
    console.log('✅ 实例表系统完全正常！');
    console.log('  • 所有设备都有正确的实例表映射');
    console.log('  • 所有实例表都按标准命名规范创建');
    console.log('  • 系统可以正常处理实例创建和查询');
  } else {
    console.log('⚠️ 系统存在配置问题，需要修复');
  }

  console.log('\n🔧 推荐操作:');
  console.log('  • 新增实例时，系统会自动使用正确的对象类实例表');
  console.log('  • 实例数据按对象类的英文名统一存储管理');
  console.log('  • 支持PBS层级信息和参数关联存储');

} catch (error) {
  console.error('验证出错:', error);
} finally {
  db.close();
}